package com.hewep.web.controller;

import java.io.File;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.hewep.kit.CacheKit;
import com.hewep.kit.Gsonkit;
import com.hewep.support.AjaxResult;
import com.hewep.support.PoiKit;
import com.hewep.support.PrintResult;
import com.hewep.support.excel.DefaultRowReader;
import com.jfinal.kit.PathKit;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.upload.UploadFile;

public class ListController extends BaseController {

	public void index() {

	}
	
	public void findByCode(){
		AjaxResult result = new AjaxResult(1);
		int pageSize = this.getParaToInt("pageSize", 10);
		Map<String, Object> org = Gsonkit.getMapObj(this.getPara("data"));
		List<Record> records = Db.find("select serviceCode,plateNo,brand,brandTypeName,outBrand,outModel "+
						"from datas where dealerCode = ?  order by outModel", org.get("dealerCode"));
		
		PrintResult printResult = new PrintResult(org, records, pageSize);
		result.setData("printResult", printResult.toJson());
		this.renderJson(result.toJson());
	}
	
	public void findAll(){
		
		AjaxResult result = new AjaxResult(1);
		int pageNumber = this.getParaToInt(0, 1);
		int sort = this.getParaToInt("sort", 0);
		int bno = this.getParaToInt("bno", 0);
		int eno = this.getParaToInt("eno", 0);
		String orgName = this.getPara("orgName", "");
		String select = "select * ";
		String sqlExceptSelect = "from org where 1=1 ";
		
		if(bno > 0){
			sqlExceptSelect += " and num >= "+bno;
		}
		if(eno >0){
			sqlExceptSelect += " and num <="+eno;
		}
		if(StrKit.notBlank(orgName)){
			sqlExceptSelect += " and orgName like '%"+orgName+"%'";
		}
		if(sort>0){
			sqlExceptSelect += " order by id desc";
		}
		Page<Record> page = Db.paginate(pageNumber, 10, select, sqlExceptSelect);
		result.setData("page", page);
		result.setData("remarks", CacheKit.getColumnRemark("org"));
		result.setData("columns", CacheKit.getColumns("org"));
		
		StringBuilder ids = new StringBuilder();
		String sql = "select id "+sqlExceptSelect;
		List<Record> records = Db.find(sql);
		for (int i=0;i<records.size(); i++) {
			Record record = records.get(i);
			if(i == 0){
				ids.append(record.getInt("id"));
			}else{
				ids.append(","+record.getInt("id"));
			}
		}
		
		result.setData("ids", ids.toString());
		this.renderJson(result.toJson());
	}
	// 上传文件
	public void uploadData() {
		AjaxResult result = new AjaxResult(1, "上传成功");
		try {

			String tempFilePath = PathKit.getWebRootPath()
					+ "\\upload\\datas.xlsx";
			File temp = new File(tempFilePath);
			if (temp.exists()) {
				temp.delete();
			}
			UploadFile file = this.getFile("file");
			file.getFile().renameTo(temp);

		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			result.setFailure("上传失败");
		}
		this.renderJson(result.toJson());
	}

	// 导入数据
	public void importData() {
		AjaxResult result = new AjaxResult(1, "上传成功");
		
		Record record = Db.findFirst("select * from datas");
		if(record != null){
			result.setFailure("请先清空数据 , 再导入");
			this.renderJson(result.toJson());
			return;
		}
		
		String tempFilePath = PathKit.getWebRootPath() + "\\upload\\datas.xlsx";
		try {
			File temp = new File(tempFilePath);
			if (temp.exists()) {
				String tableName = this.getPara("table_name", "");

				DefaultRowReader reader = new DefaultRowReader(tableName);
				PoiKit.readExcel(reader, tempFilePath);
				while(!reader.isFinished()){
					Thread.sleep(2000);
					System.out.println("上传完成, 正在统计分析....");
				}
			} else {
				result.setFailure("请上传文件, 再导入数据!");
			}
		} catch (Exception e) {
			e.printStackTrace();
			result.setFailure("上传失败:" + e.getMessage());
		}

		this.renderJson(result.toJson());
	}
	// 导出数据
	public void exportData(){
		String tempFilePath = PathKit.getWebRootPath()
				+ "\\upload\\export.xlsx";
		File temp = new File(tempFilePath);
		if (temp.exists()) {
			temp.delete();
		}
		List<Record> records = Db.find("select * from org");
		try {
			PoiKit.writeDatas(tempFilePath, CacheKit.getColumns("org"), records);
		} catch (Exception e) {
			e.printStackTrace();
		}
		this.renderFile("/upload/export.xlsx");
	}
	// 清空数据库
	public void clearData(){
		AjaxResult result = new AjaxResult(1,"清空成功");
		Integer resetNo = this.getParaToInt("reset_no", 0);
		try {
			if(resetNo == 1){
				Db.update("delete from config where item = 'HB'");
			}
			Db.update("delete from org");
			Db.update("delete from org_temp");
			Db.update("delete from datas");
			
			Db.update("delete from sqlite_sequence");
			Db.update("VACUUM");	//压缩文件
		} catch (Exception e) {
			result.setFailure(e.getMessage());
		}
		this.renderJson(result.toJson());
	}
	
	
	public void print(){
		AjaxResult result = new AjaxResult(1);
		try {
			int pageSize = this.getParaToInt("pageSize", 10);
			int orgNo = this.getParaToInt("orgNo", 1);
			String printHead = this.getPara("printHead", "");
			Record org = Db.findFirst("select * from org where id = ?", orgNo);
			List<Record> records = Db.find("select serviceCode,plateNo,brand,brandTypeName,outBrand,outModel "+
					"from datas where dealerCode = ? order by outModel", org.getStr("dealerCode"));
			org.set("printHead", printHead);
			PrintResult printResult = new PrintResult(org.getColumns(), records, pageSize);
			result.setData("printResult", printResult.toJson());
		} catch (Exception e) {
			result.setFailure(e.getMessage());
		}
		this.renderJson(result.toJson());
	}
}
